package com.dao.impl;

import com.bean.Role;
import com.bean.User;
import com.dao.UserDao;
import com.util.JDBC_Util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {
    Connection conn = null;
    ResultSet rs= null;
    PreparedStatement ps = null;

    @Override
    public List<User> getAllUsers() {
        conn = JDBC_Util.getConnection();
        List<User> list = new ArrayList<>();

        String sql = "select t_users.id, account, password, real_name from t_users";
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String account = rs.getString("account");
                String password = rs.getString("password");
                String realName = rs.getString("real_name");

                User user = new User(id,account,password,realName);
                list.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return list;
    }

    public List<User> getFindUsers(String userName){
        conn = JDBC_Util.getConnection();
        List<User> list = new ArrayList<>();

        String sql = "select t_users.id, account, password, real_name from t_users" +
                " where 1 = 1";

        if(!userName.equals("")  && userName != null){
            sql += " and account like '%"+userName+"%'";
        }

        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()){
                int id = rs.getInt("id");
                String account = rs.getString("account");
                String password = rs.getString("password");
                String realName = rs.getString("real_name");

                User user = new User(id,account,password,realName);
                list.add(user);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return list;
    }

    @Override
    public int addUser(User user) {
        String account = user.getAccount();
        String password = user.getPassword();
        String realName = user.getRealName();
        conn = JDBC_Util.getConnection();
        String sql = "insert into t_users values (null,'"+account+"','"+password+"','"+realName+"')";
        int m = 0;
        try {
            ps = conn.prepareStatement(sql);
            m = ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return m;
    }

    @Override
    public int addUser(String account, List<String> roleNames) {
        conn = JDBC_Util.getConnection();
        List<Integer> rIdList = new ArrayList<>();
        String sql1 = "select t_users.id from t_users where account = ?";
        int id = 0;
        int rid = 0;
        int m = 0;
        try {
            ps = conn.prepareStatement(sql1);
            ps.setString(1,account);
            rs = ps.executeQuery();
            if(rs.next()){
                id = rs.getInt("id");
            }
            for (String roleName:roleNames) {
                String sql2 = "select t_role.id from t_role where role_name = ?";
                ps = conn.prepareStatement(sql2);
                ps.setString(1,roleName);
                rs = ps.executeQuery();
                while (rs.next()) {
                    rid = rs.getInt("id");
                    rIdList.add(rid);
                    System.out.println(rIdList);
                }
            }

            for (int newId:rIdList) {
                String sql3 = "insert into t_users_role values (null,?,?)";
                ps = conn.prepareStatement(sql3);
                ps.setInt(1,id);
                ps.setInt(2,newId);
                m = ps.executeUpdate();
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return m;
    }

    @Override
    public int updateUserRole(int key, List<String> roleNames) {
        String str1 = "delete from t_users_role where user_id = "+key+"";
        int x = JDBC_Util.update(str1);

        conn = JDBC_Util.getConnection();
        List<Integer> rIdList = new ArrayList<>();

        int rid = 0;
        int m = 0;
        try {
            for (String roleName:roleNames) {
                String sql2 = "select t_role.id from t_role where role_name = ?";
                ps = conn.prepareStatement(sql2);
                ps.setString(1,roleName);
                rs = ps.executeQuery();
                while (rs.next()) {
                    rid = rs.getInt("id");
                    rIdList.add(rid);
                }
            }

            for (int newId:rIdList) {
                String sql3 = "insert into t_users_role values (null,?,?)";
                ps = conn.prepareStatement(sql3);
                ps.setInt(1,key);
                ps.setInt(2,newId);
                m = ps.executeUpdate();
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return m;
    }

    @Override
    public User returnUser(String account) {
        conn =JDBC_Util.getConnection();
        User user = new User();
        Role role = new Role();
        List<Role> roleList = new ArrayList<>();
        int id = 0;
        try {
            String sql = "select id, account, password, real_name from t_users where account = ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1,account);
            rs = ps.executeQuery();
            if (rs.next()) {
                id = rs.getInt("id");
                String account1 = rs.getString("account");
                String password = rs.getString("password");
                String realName = rs.getString("real_name");

                user.setId(id);
                user.setAccount(account1);
                user.setPassword(password);
                user.setRealName(realName);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return user;
    }

    @Override
    public int accountPasswordIsEqual(String account, String password) {
        conn =JDBC_Util.getConnection();
        int m = -1;
        try {
            String sql = "select count(0) from t_users where account = ? and password = ?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,account);
            ps.setString(2,password);
            rs = ps.executeQuery();
            if (rs.next()) {
                m = rs.getInt("count(0)");
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return m;
    }

    @Override
    public int deleteUser(int key) {
        String str1 = "delete from t_users_role where user_id = "+key+"";
        String str2 = "delete from t_users where id = "+key+"";
        int n = JDBC_Util.update(str1);
        int m = JDBC_Util.update(str2);
        return m;
    }

    @Override
    public int updatePassword(User user, String NewPsw) {
        int userId = user.getId();
        String sql = "update t_users set password = '"+NewPsw+"' where id = "+userId+"";
        int m = JDBC_Util.update(sql);
        return m;
    }

    public User getSelectUser(int key){
        User user = new User();

        String sql = "select account,real_name from t_users where id = "+key+"";

        conn = JDBC_Util.getConnection();
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();

            if (rs.next()) {
                String account = rs.getString("account");
                String realName = rs.getString("real_name");
                user.setAccount(account);
                user.setRealName(realName);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBC_Util.close(rs,conn,ps);
        }
        return user;
    }
}
